﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DrSoftsFrame.Servers;
using System.IO;
using DrSoftsFrame;

namespace PreeRegisterDAL
{
    public class AvailableCountDAL
    {

        public static DataSet SelectAvailableCount(string iWorkPlanID, string iYYDaySpan)
        {
            //if (!PreeRegisterDAL.LHL.AvailableCountDAL.bDataBase)
            //{
            //    return AvailableCountDAL.GetData("AvailableCount.xml");
            //}
            SqlCommand cmd = new SqlCommand();
            string strSql = @"select iWorkPlanID WorkId ,ISNULL(nMaxPreengageNum,0) OrderCount,
(isnull(nMaxPreengageNum,0)-(select COUNT(*) c 
from OPDRegisterPree  with(nolock) where iworkplanid=@iWorkPlanID and bRegisteFlag in (0,1)
) -
(select isnull(COUNT(*),0) c from opdregister  with(nolock) where iworkplanid=@iWorkPlanID)) AvailableCount
from opdworkplan  with(nolock) 
where iWorkPlanID=@iWorkPlanID and CONVERT(varchar(100), dStartTime,23)>CONVERT(varchar(100),getdate(),23)
and  CONVERT(varchar(100), dStartTime,23)<= CONVERT(varchar(100),DATEADD(dd,@iYYDaySpan,getdate()),23)";
            cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@iWorkPlanID", System.Data.SqlDbType.Int));
            cmd.Parameters["@iWorkPlanID"].Value = iWorkPlanID;
            cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@iYYDaySpan", System.Data.SqlDbType.Int));
            cmd.Parameters["@iYYDaySpan"].Value = iYYDaySpan;//可预约数查询添加预约天数验证  lhl20131213
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strSql;
            ClinicDb mydb = new ClinicDb();
            DataSet ds = mydb.ExecuteSqlCmdFillDs(cmd);
            //SaveXML("AvailableCount.xml", ds.GetXml());
            return ds;
        }

        /// <summary>
        /// 序号预约查询可预约数_同煤版  liujia150812
        /// </summary>
        /// <param name="iWorkPlanID"></param>
        /// <param name="iYYDaySpan"></param>
        /// <returns></returns>
        public static DataSet SelectAvailableCount_Tmkyy(string iWorkPlanID, string iYYDaySpan)
        {
            //if (!PreeRegisterDAL.LHL.AvailableCountDAL.bDataBase)
            //{
            //    return AvailableCountDAL.GetData("AvailableCount.xml");
            //}
            SqlCommand cmd = new SqlCommand();
            string strSql = @"select iWorkPlanID WorkId ,ISNULL(nMaxPreengageNum,0) OrderCount,
(isnull(nMaxPreengageNum,0)-(select COUNT(*) c 
from hmpreregister  with(nolock) where iworkplanid=@iWorkPlanID and isnull(bcancelflag,0) =0
) -
(select isnull(COUNT(*),0) c from opdregister  with(nolock) where iworkplanid=@iWorkPlanID)) AvailableCount
from opdworkplan  with(nolock) 
where iWorkPlanID=@iWorkPlanID and CONVERT(varchar(100), dStartTime,23)>CONVERT(varchar(100),getdate(),23)
and  CONVERT(varchar(100), dStartTime,23)<= CONVERT(varchar(100),DATEADD(dd,@iYYDaySpan,getdate()),23)";
            cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@iWorkPlanID", System.Data.SqlDbType.Int));
            cmd.Parameters["@iWorkPlanID"].Value = iWorkPlanID;
            cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@iYYDaySpan", System.Data.SqlDbType.Int));
            cmd.Parameters["@iYYDaySpan"].Value = iYYDaySpan;//可预约数查询添加预约天数验证  lhl20131213
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strSql;
            ClinicDb mydb = new ClinicDb();
            DataSet ds = mydb.ExecuteSqlCmdFillDs(cmd);
            //SaveXML("AvailableCount.xml", ds.GetXml());
            return ds;
        }

        public static DataSet SelectDetailStatus(string iWorkPlanID, string iYYDaySpan)
        {
            //if (!PreeRegisterDAL.LHL.AvailableCountDAL.bDataBase)
            //{
            //    return AvailableCountDAL.GetData("AvailableCount.xml");
            //}
            //获取可预约数时去掉休息时间号源  lhl20140719
            SqlCommand cmd = new SqlCommand();//加预约起始号源限制 lhl20140626
            string strSql = @"select a.iWorkPlanID WorkId,a.iDetailId,case when isnull(a.bPreRegister,0)=1 OR isnull(a.bCheck,0)=1 or isnull(a.bLock,0)=1 then 1 else 0 end Status 
from OPDWorkPlanDetail a with(nolock) join OPDWorkPlan b with(nolock) on a.iWorkPlanID=b.iWorkPlanID 
              where a.iWorkPlanID =@iWorkPlanID
                and  a.iNum>isnull(b.preenbegin,0) 
and (CONVERT(varchar(100),a.dStartTime, 8)>'07:00:00' and CONVERT(varchar(100),a.dStartTime, 8)<'12:00:00'
or  CONVERT(varchar(100),a.dStartTime, 8)>'14:00:00' and CONVERT(varchar(100),a.dStartTime, 8)<'18:00:00') 
               and CONVERT(varchar(100), b.dStartTime,23)>=CONVERT(varchar(100),getdate(),23)
and  CONVERT(varchar(100), b.dStartTime,23)<= CONVERT(varchar(100),DATEADD(dd,@iYYDaySpan,getdate()),23)";
            cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@iWorkPlanID", System.Data.SqlDbType.Int));
            cmd.Parameters["@iWorkPlanID"].Value = iWorkPlanID;
            cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@iYYDaySpan", System.Data.SqlDbType.Int));
            cmd.Parameters["@iYYDaySpan"].Value = iYYDaySpan;  //可预约数查询添加预约天数验证  lhl20131213
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strSql;
            ClinicDb mydb = new ClinicDb();
            DataSet ds = mydb.ExecuteSqlCmdFillDs(cmd);
            //AvailableCountDAL.SaveXML("AvailableCount.xml", ds.GetXml());
            return ds;
        }
    }
}
